﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Windows.Forms;

namespace LotteryTicket
{
    class dbOperate
    {
        private static string db_path = "";
        //SQLiteConnection conn = new SQLiteConnection(db_path);//("Data Source=E:/CODE/C#/Lottery-Ticket/LotteryTicket/Resource/lt.db");
        SQLiteConnection conn = new SQLiteConnection("Data Source=Resource/lt.db");//("Data Source=E:/CODE/C#/Lottery-Ticket/LotteryTicket/Resource/lt.db");
        SQLiteCommand cmd = new SQLiteCommand();
        public dbOperate(string path)
        {
            db_path = path;
        }
        public int db_comm()
        {
            try
            {
                
                //SQLiteConnection conn1 = new SQLiteConnection(path);//("Data Source=e:/hqhp_ST.s3db");//
                conn.Open();

            //    SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = conn;      //设置连接
            }
            catch {
                return 1;
            }
            return 0;
        }

        public void insert_data(string s)
        {
            try
            {
                //
                string[] sArray = s.Split(' ');
                cmd.CommandText = "INSERT INTO lottery (code,num1,num2,num3,lottery_date,sum,span)VALUES (" + int.Parse(sArray[1]) + ", " + int.Parse(sArray[2]) + ", " + int.Parse(sArray[3]) + ", " + int.Parse(sArray[4]) + ", '" + sArray[0] + "' ," + int.Parse(sArray[6]) + ", " + int.Parse(sArray[5]) + " )";
          //     string ss = "INSERT INTO lottery (code,num1,num2,num3,lottery_date,sum,span)VALUES (" + int.Parse(sArray[1]) + ", " + int.Parse(sArray[2]) + ", " + int.Parse(sArray[3]) + ", " + int.Parse(sArray[4]) + ", '" + sArray[0] + "' , " + int.Parse(sArray[6]) + ", " + int.Parse(sArray[5]) + " )";
        //       MessageBox.Show(ss);
               // string f = "INSERT INTO lottery (index_num,num1,num2,num3,lottery_date)VALUES (" + sArray[1] + ", " + sArray[2] + ", " + sArray[3] + ", " + sArray[4] + ", '" + sArray[0] + "' )";
                cmd.ExecuteNonQuery();
              //  string f = "INSERT INTO lottery (index_num,num1,num2,num3,lottery_date)VALUES (" + int.Parse(sArray[1]) + ", " + int.Parse(sArray[2]) + ", " + int.Parse(sArray[3]) + ", " + int.Parse(sArray[4]) + ", " + sArray[0] + " )";
              //  MessageBox.Show(f); 
               // string Name1 = cmd.ExecuteScalar().ToString();
                return;
             
            }
            catch (System.Exception ex)
            {
                // MessageTextBox.Text = ex.Message;
                MessageBox.Show("异常：" + ex.Message + "\n");
            }
        }
        
        public int read_count(int num1,int num2,int num3)
        {
            int res = -1;
            try
            {
                //cmd.CommandText = "select current_account from system_status_flag where ID=1";
                //string account = cmd.ExecuteScalar().ToString();
                //int idd = Convert.ToInt32(account);//字符串转int

                cmd.CommandText = "select count(*) from lottery ";
                string total = cmd.ExecuteScalar().ToString();

                cmd.CommandText = "select count(*) from lottery where num1="+num1+" and num2 ="+num2+" and num3 ="+num3;
                string user = cmd.ExecuteScalar().ToString();
               // MessageBox.Show("total:"+total+" times:"+user);
                res = int.Parse(user);

                return res;

            }
            catch (System.Exception ex)
            {
                // MessageTextBox.Text = ex.Message;
                MessageBox.Show("异常：" + ex.Message + "\n");
                return res;
            }
            return res;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="id_now"></param>
        public void cal_sum_same(int id_now)
        {
            int id = 1;
            int sum = -1;
            int sum_before = -1;
            int count = 0;
            id= id_now;
            try
            {
                cmd.CommandText = "select sum from lottery where id = "+id_now;
                SQLiteDataReader reader = cmd.ExecuteReader();
                reader.Read();
                sum = int.Parse(reader["sum"].ToString());
                reader.Dispose();
                while (true) { 
                    id --;
                    if(id==0)
                        break;
                    cmd.CommandText = "select sum from lottery where id = "+id;
                    
                    reader = cmd.ExecuteReader();
                    reader.Read();
                    sum_before = int.Parse(reader["sum"].ToString());
                    reader.Dispose();
                    if (sum == sum_before) {
                        count ++;
                    }else{
                        cmd.CommandText = "update lottery set sum_same = "+ count +" where id = " + id_now;
                        cmd.ExecuteNonQuery();
                        break;
                    }
                    
                }
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("异常：" + ex.Message + "\n");
            }
        }
        
        /// <summary>
        /// 
        /// </summary>
        /// <param name="id_now"></param>
        public void cal_span_same(int id_now)
        {
            int id = 1;
            int span = -1;
            int span_before = -1;
            int count = 0;
            id = id_now;
            try
            {
                cmd.CommandText = "select span from lottery where id = " + id_now;
                SQLiteDataReader reader = cmd.ExecuteReader();
                reader.Read();
                span = int.Parse(reader["span"].ToString());
                reader.Dispose();

                while (true)
                {
                    id--;
                    if (id == 0)
                        break;
                    cmd.CommandText = "select span from lottery where id = " + id;

                    reader = cmd.ExecuteReader();
                    reader.Read();
                    span_before = int.Parse(reader["span"].ToString());
                    reader.Dispose();
                    if (span == span_before)
                    {
                        count++;
                    }
                    else
                    {
                        cmd.CommandText = "update lottery set span_same = " + count + " where id = " + id_now;
                        cmd.ExecuteNonQuery();
                        break;
                    }

                }
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("异常：" + ex.Message + "\n");
            }
        }
        
        /// <summary>
        /// 
        /// </summary>
        /// <param name="id_now"></param>
        public void cal_num1_same(int id_now)
        {
            int id = 1;
            int num1 = -1;
            int num1_before = -1;
            int count = 0;
            id = id_now;
            try
            {
                cmd.CommandText = "select num1 from lottery where id = " + id_now;
                SQLiteDataReader reader = cmd.ExecuteReader();
                reader.Read();
                num1 = int.Parse(reader["num1"].ToString());
                reader.Dispose();

                while (true)
                {
                    id--;
                    if (id == 0)
                        break;
                    cmd.CommandText = "select num1 from lottery where id = " + id;

                    reader = cmd.ExecuteReader();
                    reader.Read();
                    num1_before = int.Parse(reader["num1"].ToString());
                    reader.Dispose();
                    if (num1 == num1_before)
                    {
                        count++;
                    }
                    else
                    {
                        cmd.CommandText = "update lottery set num1_same = " + count + " where id = " + id_now;
                        cmd.ExecuteNonQuery();
                        break;
                    }

                }
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("异常：" + ex.Message + "\n");
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="id_now"></param>
        public void cal_num2_same(int id_now)
        {
            int id = 1;
            int num2 = -1;
            int num2_before = -1;
            int count = 0;
            id = id_now;
            try
            {
                cmd.CommandText = "select num2 from lottery where id = " + id_now;
                SQLiteDataReader reader = cmd.ExecuteReader();
                reader.Read();
                num2 = int.Parse(reader["num2"].ToString());
                reader.Dispose();

                while (true)
                {
                    id--;
                    if (id == 0)
                        break;
                    cmd.CommandText = "select num2 from lottery where id = " + id;

                    reader = cmd.ExecuteReader();
                    reader.Read();
                    num2_before = int.Parse(reader["num2"].ToString());
                    reader.Dispose();
                    if (num2 == num2_before)
                    {
                        count++;
                    }
                    else
                    {
                        cmd.CommandText = "update lottery set num2_same = " + count + " where id = " + id_now;
                        cmd.ExecuteNonQuery();
                        break;
                    }
                }
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("异常：" + ex.Message + "\n");
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="id_now"></param>
        public void cal_num3_same(int id_now)
        {
            int id = 1;
            int num3 = -1;
            int num3_before = -1;
            int count = 0;
            id = id_now;
            try
            {
                cmd.CommandText = "select num3 from lottery where id = " + id_now;
                SQLiteDataReader reader = cmd.ExecuteReader();
                reader.Read();
                num3 = int.Parse(reader["num3"].ToString());
                reader.Dispose();

                while (true)
                {
                    id--;
                    if (id == 0)
                        break;
                    cmd.CommandText = "select num3 from lottery where id = " + id;

                    reader = cmd.ExecuteReader();
                    reader.Read();
                    num3_before = int.Parse(reader["num3"].ToString());
                    reader.Dispose();
                    if (num3 == num3_before)
                    {
                        count++;
                    }
                    else
                    {
                        cmd.CommandText = "update lottery set num3_same = " + count + " where id = " + id_now;
                        cmd.ExecuteNonQuery();
                        break;
                    }
                }
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("异常：" + ex.Message + "\n");
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="id_now"></param>
        public void cal_appear_same(int id_now)
        {
            int id = 1;
            int[] num = new int[3];
            int[] num_before = new int[3];
            int count = 0;
            id = id_now;
            try
            {
                cmd.CommandText = "select num1,num2,num3 from lottery where id = "+id_now;
                SQLiteDataReader reader = cmd.ExecuteReader();
                reader.Read();
                num[0] = int.Parse(reader["num1"].ToString());
                num[1] = int.Parse(reader["num2"].ToString());
                num[2] = int.Parse(reader["num3"].ToString());
                reader.Dispose();

                while (true)
                {
                    id--;
                    if (id == 0)
                        break;

                    cmd.CommandText = "select num1,num2,num3 from lottery where id = " + id;
                    reader = cmd.ExecuteReader();
                    reader.Read();
                    num_before[0] = int.Parse(reader["num1"].ToString());
                    num_before[1] = int.Parse(reader["num2"].ToString());
                    num_before[2] = int.Parse(reader["num3"].ToString());
                    reader.Dispose();

                    if (id_now < 100)
                        break;

                    if ((num_before[0] == num[0]) && (num_before[1] == num[1]) && (num_before[2] == num[2]))
                    {
                        count++;
                    }

                    if (100 <= id_now) {
                        if ((id_now - id) == 100)
                        {
                            cmd.CommandText = "update lottery set appear100 = " + count + " where id = " + id_now;
                            cmd.ExecuteNonQuery();
                            if (300 > id_now)
                                break;
                        }
                    } 
                    if (300 <= id_now) {
                        if ((id_now - id) == 300)
                        {
                            cmd.CommandText = "update lottery set appear300 = " + count + " where id = " + id_now;
                            cmd.ExecuteNonQuery();
                            if (500 > id_now)
                                break;
                        }
                    } 
                    if (500 <= id_now) {
                        if ((id_now - id) == 500)
                        {
                            cmd.CommandText = "update lottery set appear500 = " + count + " where id = " + id_now;
                            cmd.ExecuteNonQuery();
                            break;
                        }
                    }
                }
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("异常：" + ex.Message + "\n");
            }
        }

        /// <summary>
        /// 计算 上期出现过的数的个数
        /// </summary>
        /// <param name="id_now"></param>
        public void cal_all_num_appear_same(int id_now)
        {
            int id = 1;
            int[] num = new int[3];
            int[] num_before = new int[3];
            int count = 0;
            id = id_now - 1;
            try
            {
                cmd.CommandText = "select num1,num2,num3 from lottery where id = " + id_now;
                SQLiteDataReader reader = cmd.ExecuteReader();
                reader.Read();
                num[0] = int.Parse(reader["num1"].ToString());
                num[1] = int.Parse(reader["num2"].ToString());
                num[2] = int.Parse(reader["num3"].ToString());
                reader.Dispose();

                cmd.CommandText = "select num1,num2,num3 from lottery where id = " + id;
                reader = cmd.ExecuteReader();
                reader.Read();
                num_before[0] = int.Parse(reader["num1"].ToString());
                num_before[1] = int.Parse(reader["num2"].ToString());
                num_before[2] = int.Parse(reader["num3"].ToString());
                reader.Dispose();

                for (int i = 0; i < 3; i++)
                {
                    if ((num_before[0] == num[i]) || (num_before[1] == num[i]) || (num_before[2] == num[i]))
                    { count++; }
                }
                cmd.CommandText = "update lottery set all_num_same = " + count + " where id = " + id_now;
                cmd.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("异常：" + ex.Message + "\n");
            }
        }

        public string export_to_file(int id)
        {
            int[] num = new int[3];
            int[] num_before = new int[3];
            string line="";
            try
            {
                cmd.CommandText = "select * from lottery where id = " + id;
                SQLiteDataReader reader = cmd.ExecuteReader();
                reader.Read();
                num[0] = int.Parse(reader["appear100"].ToString());
                num[1] = int.Parse(reader["appear300"].ToString());
                num[2] = int.Parse(reader["appear500"].ToString());

                if (num[0] == 255) num[0] = -1;
                if (num[1] == 255) num[1] = -1;
                if (num[2] == 255) num[2] = -1;

                line = reader["id"].ToString()+"&"+reader["code"].ToString()+"&"+num[0]+"|"+num[1]+"|"+num[2];
                reader.Dispose();
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("异常：" + ex.Message + "\n");
            }
            return line;
        }
        /// <summary>
        /// 计算出，近300 期没出现过的号码 
        /// </summary>
        /// <returns>返回所得的号码列表</returns>
        public List<String> get_disappear300()
        {
            int i, j, k;
            int id = 0;
            List<String> code = new List<string>();

            try
            {
                cmd.CommandText = "select max(id) from lottery";
                //string max_id = cmd.ExecuteScalar().ToString();
                id = int.Parse(cmd.ExecuteScalar().ToString());

                for (i = 0; i <= 9; i++)
                    for (j = 0; j <= 9; j++)
                        for (k = 0; k <= 9; k++)
                        {

                            cmd.CommandText = "select count(*) from lottery where num1=" + i + " and num2 =" + j + " and num3 =" + k + " and id< " + (id - 300);
                            string user = cmd.ExecuteScalar().ToString();
                            // MessageBox.Show("total:"+total+" times:"+user);
                            if (int.Parse(user) == 0)
                            {
                                code.Add(i + " " + j + " " + k);
                            }
                        }
                cmd.CommandText = "select count(*) from lottery where appear300 != -1";
                string total = cmd.ExecuteScalar().ToString();
                code.Add("total:" + total);
                cmd.CommandText = "select count(*) from lottery where appear300 = 0";
                string total300 = cmd.ExecuteScalar().ToString();
                code.Add("total300:" + total300);
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("异常：" + ex.Message + "\n");
            }
            return code;
        }

        /// <summary>
        /// 计算 近300期
        /// </summary>
        /// <returns>概率放大10000</returns>
        public int get_appear300_probability()
        {
            int id = 0;
      //      int total = 0;
            int appear300 = 0;
            int probalility = 0;

            try
            {
            cmd.CommandText = "select max(id) from lottery";
            id = int.Parse(cmd.ExecuteScalar().ToString());

            cmd.CommandText = "select count(*) from lottery where appear300 != -1";
            int total300 = int.Parse(cmd.ExecuteScalar().ToString());

                cmd.CommandText = "select appear300 from lottery where id = "+(id -1);//上一期情况
                int last = int.Parse(cmd.ExecuteScalar().ToString());
                if (last == 0) {
                    cmd.CommandText = "select count(*) from lottery where appear300 == 0";
                    appear300 = int.Parse(cmd.ExecuteScalar().ToString());

                    probalility = appear300 * 10000 / total300;//放大10000
                }
                else if (last == 1) {
                    cmd.CommandText = "select count(*) from lottery where appear300 >= 2";
                    appear300 = int.Parse(cmd.ExecuteScalar().ToString());
                    probalility = (total300 - appear300) * 10000 / total300;
                }
                else if (last >= 2)
                {
                    cmd.CommandText = "select count(*) from lottery where appear300 >= 3";
                    appear300 = int.Parse(cmd.ExecuteScalar().ToString());

                    probalility = (total300 - appear300) * 10000 / total300;
                }
                else {
                    MessageBox.Show("上期数据异常!!!");
                }
                
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("异常：" + ex.Message + "\n");
            }
            return probalility;
        }

        /// <summary>
        /// 计算和值相同的概率
        /// </summary>
        /// <returns></returns>
        public int get_sum_same_probability()
        {
            int id = 0;
            int total = 0;
            int sum = 0;
            int sum_same =0;
            int probalility = 0;

            try
            {
                cmd.CommandText = "select count(*) from lottery";
                total = int.Parse(cmd.ExecuteScalar().ToString());

                cmd.CommandText = "select max(id) from lottery";
                id = int.Parse(cmd.ExecuteScalar().ToString());

                cmd.CommandText = "select sum_same from lottery where id = "+id;
                sum_same = int.Parse(cmd.ExecuteScalar().ToString());

                if (sum_same == 0)
                {
                    cmd.CommandText = "select count(*) from lottery where appear300 == 0";
                    sum = int.Parse(cmd.ExecuteScalar().ToString());

                    probalility = sum * 10000 / total;//放大10000
                }
                else if (sum_same >= 1)
                {
                    cmd.CommandText = "select count(*) from lottery where sum_same >= 1";
                    sum = int.Parse(cmd.ExecuteScalar().ToString());
                    probalility = sum * 10000 / total;//放大10000
                }
                else
                {
                    MessageBox.Show("上期数据异常!!!");
                }

            }
            catch (System.Exception ex)
            {
                MessageBox.Show("异常：" + ex.Message + "\n");
            }
            return probalility;
        }

        /// <summary>
        /// 获取上期sum值
        /// </summary>
        /// <returns></returns>
        public int get_sum_val()
        {
            int id = 0;
            int sum = -1;

            try
            {
                cmd.CommandText = "select max(id) from lottery";
                id = int.Parse(cmd.ExecuteScalar().ToString());

                cmd.CommandText = "select sum from lottery where id = " + id;
                sum = int.Parse(cmd.ExecuteScalar().ToString());

            }
            catch (System.Exception ex)
            {
                MessageBox.Show("异常：" + ex.Message + "\n");
            }
            return sum;
        }
        /// <summary>
        /// 
        /// </summary>
        public void close_db()
        {
            conn.Clone();
        }
        private void show_init_config()
        {
            SQLiteConnection conn = new SQLiteConnection("Data Source=E:/CODE/C#/Lottery-Ticket/LotteryTicket/Resource/lt.db");//("Data Source=e:/hqhp_ST.s3db");//
            conn.Open();

            SQLiteCommand cmd = new SQLiteCommand();
            cmd.Connection = conn;      //设置连接
            try
            {
                //读取用户名和密码状态
                cmd.CommandText = "select save_user_name from system_status_flag where ID=1";
                string Name1 = cmd.ExecuteScalar().ToString();

                cmd.CommandText = "select save_password from system_status_flag where ID=1";
                string password = cmd.ExecuteScalar().ToString();

                //cmd.CommandText = "select current_account from system_status_flag where ID=1";
                //string account = cmd.ExecuteScalar().ToString();
                //int idd = Convert.ToInt32(account);//字符串转int

                cmd.CommandText = "select user_name from temp_account where ID=1";
                string user = cmd.ExecuteScalar().ToString();
                cmd.CommandText = "select user_password from temp_account where ID=1";
                string key = cmd.ExecuteScalar().ToString();

                if (Name1 == "Y")
                {
                  
                }
                else
                {
                   
                }


            }
            catch (System.Exception ex)
            {
                // MessageTextBox.Text = ex.Message;
                MessageBox.Show("异常：" + ex.Message + "\n");
            }
        }
    }
}
